package cn.slt.service.impl;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.slt.service.ITestExportExcelService;
import cn.slt.service.MD_MetadataService;
import cn.slt.service.MD_ObjDSIDService;
import cn.slt.service.MD_classDSIDService;
import cn.slt.service.MD_libraryDSIDService;
import cn.slt.util.ExportUtil;
import cn.slt.domian.*;
import cn.slt.mapper.TestExportExcelDaoImpl;

@Service
public class TestExportExcelServiceImpl implements ITestExportExcelService {
	@Autowired
	private TestExportExcelDaoImpl dao;
	@Autowired
	private MD_classDSIDService classDsid;
	@Autowired
	private MD_libraryDSIDService libDSID;
	@Autowired
	private MD_ObjDSIDService objDSID;
	@Autowired
	private MD_MetadataService metadata;

	public void exportExcel1(String hql, String str, String[] titles, ServletOutputStream outputStream) {
		List<MD_libraryDSID> list = dao.exportExcel1(hql, str);
		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("导出到excel");
		ExportUtil exportUtil = new ExportUtil(workBook, sheet);
		XSSFCellStyle headStyle = exportUtil.getHeadStyle();
		XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);
		}
		// 构建表体数据
		if (list != null && list.size() > 0) {
			for (int j = 0; j < list.size(); j++) {
				XSSFRow bodyRow = sheet.createRow(j + 1);
				MD_libraryDSID goods = list.get(j);

				cell = bodyRow.createCell(0);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getMdCode());

				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsCode());
				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsName());
				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				if (goods.getStartTime() == null)
					cell.setCellValue("");
				else
					cell.setCellValue(goods.getStartTime());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBuildUnit());
				cell = bodyRow.createCell(5);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getStaff());
				cell = bodyRow.createCell(6);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBuildDate());
				cell = bodyRow.createCell(7);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getMangUnit());
				cell = bodyRow.createCell(8);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getMangStaff());
				cell = bodyRow.createCell(9);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getStandname());
				cell = bodyRow.createCell(10);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getService());
				cell = bodyRow.createCell(11);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getLocation());
				cell = bodyRow.createCell(12);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getCharSet());
				cell = bodyRow.createCell(13);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getKeywords());
				cell = bodyRow.createCell(14);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBeizhu());
				cell = bodyRow.createCell(15);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getVersion());
				cell = bodyRow.createCell(16);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getAttention());
				cell = bodyRow.createCell(17);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getUseconstr());
				cell = bodyRow.createCell(18);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getSeclevl());
				cell = bodyRow.createCell(19);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getOtherconstr());
				cell = bodyRow.createCell(20);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getExchangeEnv());

			}
		}
		try {
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	public void exportExcel2(String hql, String str, String[] titles, ServletOutputStream outputStream) {

		List<MD_ClassDSID> list = dao.exportExcel2(hql, str);

		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("导出到excel");
		ExportUtil exportUtil = new ExportUtil(workBook, sheet);
		XSSFCellStyle headStyle = exportUtil.getHeadStyle();
		XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);
		}
		// 构建表体数据
		if (list != null && list.size() > 0) {
			for (int j = 0; j < list.size(); j++) {
				XSSFRow bodyRow = sheet.createRow(j + 1);
				MD_ClassDSID goods = list.get(j);

				cell = bodyRow.createCell(0);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getMdCode());

				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getSupDsCode());

				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsCode());

				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsName());

				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBuildDate());

				cell = bodyRow.createCell(5);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBeizhu());

				cell = bodyRow.createCell(6);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getKeywords());

			}
		}
		try {
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	public void exportExcel3(String hql, String str, String[] titles, ServletOutputStream outputStream) {

		List<MD_ObjDSID> list = dao.exportExcel3(hql, str);
		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("导出到excel");
		ExportUtil exportUtil = new ExportUtil(workBook, sheet);
		XSSFCellStyle headStyle = exportUtil.getHeadStyle();
		XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);
		}
		// 构建表体数据
		if (list != null && list.size() > 0) {
			for (int j = 0; j < list.size(); j++) {
				XSSFRow bodyRow = sheet.createRow(j + 1);
				MD_ObjDSID goods = list.get(j);

				cell = bodyRow.createCell(0);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getMdCode());

				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getSupDsCode());

				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsCode());
				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDsName());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getIspk());
				cell = bodyRow.createCell(5);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getDataType());
				cell = bodyRow.createCell(6);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBuildDate());
				cell = bodyRow.createCell(7);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getBeizhu());
				cell = bodyRow.createCell(8);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(goods.getComments());

			}
		}
		try {
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	public void exportExcel4(String location, String owner, String[] titles, ServletOutputStream outputStream) {
		Map<String, String> bumenMap = new HashMap<String, String>();
		bumenMap.put("location", location);
		bumenMap.put("dsName", owner);
		int supdscode = libDSID.getdscode(bumenMap);
		List<MD_ClassDSID> tableDs = classDsid.getBySupdscode(supdscode);
		// System.out.println(tableDs);
		// List<String> tables = PipeiTest.getTables();

		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		CreationHelper creationHelper = workBook.getCreationHelper();
		XSSFFont hlinkfont = workBook.createFont();
		hlinkfont.setUnderline(XSSFFont.U_SINGLE);
		hlinkfont.setColor(HSSFColor.BLUE.index);
		XSSFCellStyle hlinkstyle = workBook.createCellStyle();
		hlinkstyle.setFont(hlinkfont);
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("table");
		ExportUtil exportUtil = new ExportUtil(workBook, sheet);
		XSSFCellStyle headStyle = exportUtil.getHeadStyle();
		XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);
		}
		// 构建tables表体数据
		int i = 0;
		for (MD_ClassDSID table : tableDs) {
			if (table.getBeizhu() == null || table.getKeywords() == null || table.getBeizhu().equals("")
					|| table.getKeywords().equals("")) {
				XSSFRow bodyRow = sheet.createRow(i++ + 1);
				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(table.getDsCode());

				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(table.getDsName());
				XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
				// "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
				hyperlink.setAddress("#" + table.getDsName() + "!D2");
				cell.setHyperlink(hyperlink);
				cell.setCellStyle(hlinkstyle);

				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(table.getBeizhu());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(table.getKeywords());
			}
		}

		for (MD_ClassDSID table : tableDs) {
			sheet = workBook.createSheet(table.getDsName());
			exportUtil = new ExportUtil(workBook, sheet);
			headStyle = exportUtil.getHeadStyle();
			bodyStyle = exportUtil.getBodyStyle();
			// 构建表头
			headRow = sheet.createRow(0);
			cell = null;
			for (i = 0; i < titles.length; i++) {
				cell = headRow.createCell(i);
				cell.setCellStyle(headStyle);
				cell.setCellValue(titles[i]);
				if (i == 0) {
					XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
					// "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
					hyperlink.setAddress("#table!C1");
					cell.setHyperlink(hyperlink);
					cell.setCellStyle(hlinkstyle);
				}
			}
			// 构建tables表体数据
			i = 0;
			List<MD_ObjDSID> colums = objDSID.getOjbDsids(table.getDsCode());
			for (MD_ObjDSID colum : colums) {
				XSSFRow bodyRow = sheet.createRow(i++ + 1);
				cell = bodyRow.createCell(0);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getSupDsCode());
				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getDsCode());
				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getDsName());
				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getBeizhu());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getComments());
			}
		}

		try {
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

	public void exportExcel5(ServletOutputStream outputStream) {

		String[] bumen = { "办公室", "政法处", "规计处", "水资源处", "工管处", "基建处", "科技处", "财审处", "农水处", "移民办", "防办", "水政总队", "建设局",
				"南水北调", "水文局", "河道局" };
		Map<String, String> bumenMap = new HashMap<String, String>();
		// bumenMap.put("location", location);
		// bumenMap.put("dsName", owner);
		String[] titles = { "supdscode", "dscode", "表/字段名", "中文解释", "备注" };

		int supdscode = libDSID.getdscode(bumenMap);

		List<MD_ClassDSID> tableDs = classDsid.getBySupdscode(supdscode);
		// System.out.println(tableDs);
		// List<String> tables = PipeiTest.getTables();

		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workBook = new XSSFWorkbook();
		CreationHelper creationHelper = workBook.getCreationHelper();
		XSSFFont hlinkfont = workBook.createFont();
		hlinkfont.setUnderline(XSSFFont.U_SINGLE);
		hlinkfont.setColor(HSSFColor.BLUE.index);
		XSSFCellStyle hlinkstyle = workBook.createCellStyle();
		hlinkstyle.setFont(hlinkfont);
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet("table");
		ExportUtil exportUtil = new ExportUtil(workBook, sheet);
		XSSFCellStyle headStyle = exportUtil.getHeadStyle();
		XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		int i = 0;
		MD_Metadata meta = null;
		for (int j = 0; j < titles.length; j++) {
			sheet = workBook.createSheet(bumen[j]);
			exportUtil = new ExportUtil(workBook, sheet);
			headStyle = exportUtil.getHeadStyle();
			bodyStyle = exportUtil.getBodyStyle();
			// 构建表头
			headRow = sheet.createRow(0);
			cell = null;
			for (i = 0; i < titles.length; i++) {
				cell = headRow.createCell(i);
				cell.setCellStyle(headStyle);
				cell.setCellValue(titles[i]);
			}
			List<MD_libraryDSID> md_libraryDSIDs = libDSID.getall(bumen[j]);
			if (md_libraryDSIDs.size() == 0)
				continue;

			// 构建tables表体数据
			i = 0; // int i = 0;
			for (MD_libraryDSID md_libraryDSID : md_libraryDSIDs) {
				meta = metadata.getone(md_libraryDSID.getLocation(), md_libraryDSID.getUsername());
				XSSFRow bodyRow = sheet.createRow(i++ + 1);
				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(meta.getMdName());

				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getStaff());
				XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
				// "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
				hyperlink.setAddress("#" + md_libraryDSID.getDsName() + "!D2");
				cell.setHyperlink(hyperlink);
				cell.setCellStyle(hlinkstyle);

				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getBuildUnit());

				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String str = sdf.format(md_libraryDSID.getBuildDate());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(str);
				
				cell = bodyRow.createCell(5);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getMangStaff());
				
				cell = bodyRow.createCell(6);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getMangUnit());
                
				str = sdf.format(md_libraryDSID.getStartTime());
				cell = bodyRow.createCell(7);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(str);

				cell = bodyRow.createCell(8);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getStandname());
				 
				cell = bodyRow.createCell(9);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getBeizhu());
				
				cell = bodyRow.createCell(10);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(md_libraryDSID.getKeywords());
				
				cell = bodyRow.createCell(11);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(str);
				
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(str);
				
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(str);
			}
		}

		String[] titles2 = { "supdscode", "dscode", "表/字段名", "中文解释", "备注", "创建时间" };
		headRow = sheet.createRow(sheet.getLastRowNum() + 1); // 加表头
		cell = null;
		for (i = 0; i < titles2.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles2[i]);
		} //

		for (MD_ClassDSID table : tableDs) {
			sheet = workBook.createSheet(table.getDsName());
			exportUtil = new ExportUtil(workBook, sheet);
			headStyle = exportUtil.getHeadStyle();
			bodyStyle = exportUtil.getBodyStyle();
			// 构建表头
			headRow = sheet.createRow(0);
			cell = null;
			for (i = 0; i < titles2.length; i++) {
				cell = headRow.createCell(i);
				cell.setCellStyle(headStyle);
				cell.setCellValue(titles2[i]);
				if (i == 0) {
					XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
					// "#"表示本文档 "明细页面"表示sheet页名称 "A10"表示第几列第几行
					hyperlink.setAddress("#table!C1");
					cell.setHyperlink(hyperlink);
					cell.setCellStyle(hlinkstyle);
				}
			}

			// 构建tables表体数据
			i = 0;
			List<MD_ObjDSID> colums = objDSID.getOjbDsids(table.getDsCode());
			for (MD_ObjDSID colum : colums) {
				XSSFRow bodyRow = sheet.createRow(i++ + 1);
				cell = bodyRow.createCell(0);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getSupDsCode());
				cell = bodyRow.createCell(1);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getDsCode());
				cell = bodyRow.createCell(2);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getDsName());
				cell = bodyRow.createCell(3);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getBeizhu());
				cell = bodyRow.createCell(4);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(colum.getComments());
				// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				// String str = sdf.format(colum.getBuildDate());
			}
		}

		try {
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}
}